﻿namespace AnyOffice.DAL
{
    using AnyOffice.Model;
    using Microsoft.ApplicationBlocks.Data;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;

    public class DiaryDao
    {
        public bool Delete(SqlConnection conn, int diaryId, string userId, int lockTime)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("IF EXISTS( ");
            builder.Append("\tSELECT ");
            builder.Append("\t\t[DiaryId] ");
            builder.Append("\tFROM ");
            builder.Append("\t\t[dbo].[Diary] ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[DiaryId] = @DiaryId ");
            builder.Append("\t\tAND [UserId] = @UserId ");
            builder.Append("\t\tAND ");
            builder.Append("\t\t\t( ");
            builder.Append("\t\t\t\t@LockTime = 0 ");
            builder.Append("\t\t\t\tOR DateDiff(day,[AddDate], GETDATE()) < @LockTime ");
            builder.Append("\t\t\t) ");
            builder.Append("\t) ");
            builder.Append("BEGIN ");
            builder.Append("\tUPDATE ");
            builder.Append("\t\t[dbo].[Attachment] ");
            builder.Append("\tSET ");
            builder.Append("\t\t[DeleteFlag] = 1 ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[AttachmentId] IN ");
            builder.Append("\t\t\t\t( ");
            builder.Append("\t\t\t\t\tSELECT ");
            builder.Append("\t\t\t\t\t\t[AttachmentId] ");
            builder.Append("\t\t\t\t\tFROM ");
            builder.Append("\t\t\t\t\t\t[dbo].[Diary] ");
            builder.Append("\t\t\t\t\tWHERE ");
            builder.Append("\t\t\t\t\t\t[DiaryId] = @DiaryId ");
            builder.Append("\t\t\t\t\t\tAND [UserId] = @UserId ");
            builder.Append("\t\t\t\t\t\tAND ISNULL([AttachmentId],'') <> '' ");
            builder.Append("\t\t\t\t) ");
            builder.Append("\tDELETE FROM [dbo].[Diary_Comment] ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[DiaryId] = @DiaryId ");
            builder.Append("\t ");
            builder.Append("\tDELETE FROM [dbo].[Diary] ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[DiaryId] = @DiaryId ");
            builder.Append("\t\tAND [UserId] = @UserId ");
            builder.Append("END ");
            SqlParameter[] commandParameters = new SqlParameter[3];
            commandParameters[0] = new SqlParameter("@DiaryId", SqlDbType.Int);
            commandParameters[0].Value = diaryId;
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[1].Value = userId;
            commandParameters[2] = new SqlParameter("@LockTime", SqlDbType.Int);
            commandParameters[2].Value = lockTime;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public int GetLockTime(SqlConnection conn)
        {
            string commandText = "SELECT [ParaValue] FROM [dbo].[Sys_Para] WHERE [ParaName]='DIARY_LOCK_TIME'";
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, commandText);
            if (obj2 != null)
            {
                return Convert.ToInt32(obj2);
            }
            return 0;
        }

        public int Insert(SqlConnection conn, DiaryData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("INSERT INTO [dbo].[Diary] ");
            builder.Append("( ");
            builder.Append("\t[UserId], ");
            builder.Append("\t[AddDate], ");
            builder.Append("\t[Type], ");
            builder.Append("\t[AttachmentId], ");
            builder.Append("\t[Content] ");
            builder.Append(") ");
            builder.Append("VALUES ");
            builder.Append("( ");
            builder.Append("\t@UserId, ");
            builder.Append("\t@AddDate, ");
            builder.Append("\t@Type, ");
            builder.Append("\t@AttachmentId, ");
            builder.Append("\t@Content ");
            builder.Append(") ");
            builder.Append("SELECT SCOPE_IDENTITY() AS RetValue ");
            SqlParameter[] commandParameters = new SqlParameter[5];
            commandParameters[0] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[0].Value = data.UserId;
            commandParameters[1] = new SqlParameter("@AddDate", SqlDbType.DateTime);
            commandParameters[1].Value = data.AddDate;
            commandParameters[2] = new SqlParameter("@Type", SqlDbType.Int);
            commandParameters[2].Value = data.Type;
            commandParameters[3] = new SqlParameter("@AttachmentId", SqlDbType.NVarChar, 50);
            if (data.AttachmentIdIsNull)
            {
                commandParameters[3].Value = DBNull.Value;
            }
            else
            {
                commandParameters[3].Value = data.AttachmentId;
            }
            commandParameters[4] = new SqlParameter("@Content", SqlDbType.NText);
            commandParameters[4].Value = data.Content;
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, builder.ToString(), commandParameters);
            if (obj2 != null)
            {
                return Convert.ToInt32(obj2);
            }
            return -1;
        }

        public DataTable SelectByDiaryId(SqlConnection conn, int diaryId, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\t[DiaryId], ");
            builder.Append("\t[UserId], ");
            builder.Append("\t[AddDate], ");
            builder.Append("\t[Type], ");
            builder.Append("\t[AttachmentId], ");
            builder.Append("\t[Content] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Diary] ");
            builder.Append("WHERE ");
            builder.Append("\t[DiaryId] = @DiaryId ");
            builder.Append("\tAND [UserId] = @UserId ");
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@DiaryId", SqlDbType.Int);
            commandParameters[0].Value = diaryId;
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[1].Value = userId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Diary";
            return set.Tables[0];
        }

        public DataTable SelectTop10(SqlConnection conn, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT  TOP 10 ");
            builder.Append("\t[DiaryId], ");
            builder.Append("\t[UserId], ");
            builder.Append("\t[AddDate], ");
            builder.Append("\t[Type], ");
            builder.Append("\t[AttachmentId], ");
            builder.Append("\t[Content] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Diary] ");
            builder.Append("WHERE ");
            builder.Append("\t[UserId] = @UserId ");
            builder.Append("ORDER BY ");
            builder.Append("\t[AddDate] DESC, ");
            builder.Append("\t[DiaryId] DESC ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@UserId", SqlDbType.NVarChar, 20) };
            commandParameters[0].Value = userId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Diary";
            return set.Tables[0];
        }

        public void SetLockTime(SqlConnection conn, int lockTime)
        {
            string commandText = "UPDATE [Sys_Para] SET [ParaValue] = '" + lockTime.ToString() + "'  WHERE [ParaName]='DIARY_LOCK_TIME'";
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, commandText);
        }

        public bool Update(SqlConnection conn, DiaryData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE [dbo].[Diary] ");
            builder.Append("SET ");
            builder.Append("\t[AddDate] = @AddDate, ");
            builder.Append("\t[Type] = @Type, ");
            builder.Append("\t[AttachmentId] = @AttachmentId, ");
            builder.Append("\t[Content] = @Content\t ");
            builder.Append("WHERE ");
            builder.Append("\t[DiaryId] = @DiaryId ");
            builder.Append("\tAND  [UserId] = @UserId ");
            SqlParameter[] commandParameters = new SqlParameter[6];
            commandParameters[0] = new SqlParameter("@DiaryId", SqlDbType.Int);
            commandParameters[0].Value = data.DiaryId;
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[1].Value = data.UserId;
            commandParameters[2] = new SqlParameter("@AddDate", SqlDbType.DateTime);
            commandParameters[2].Value = data.AddDate;
            commandParameters[3] = new SqlParameter("@Type", SqlDbType.Int);
            commandParameters[3].Value = data.Type;
            commandParameters[4] = new SqlParameter("@AttachmentId", SqlDbType.NVarChar, 50);
            if (data.AttachmentIdIsNull)
            {
                commandParameters[4].Value = DBNull.Value;
            }
            else
            {
                commandParameters[4].Value = data.AttachmentId;
            }
            commandParameters[5] = new SqlParameter("@Content", SqlDbType.NText);
            commandParameters[5].Value = data.Content;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }
    }
}

